﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_School_User类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_School_UserService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_School_UserService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_School_User
        public IList<HW_School_User> MapperList(SqlDataReader reader)
         {
            IList<HW_School_User> list = new List<HW_School_User>();
            HW_School_User obj=null;
            while (reader.Read())
            {
                obj = new HW_School_User();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.SchoollCode = (string)reader["SchoollCode"];
                obj.RoleId = reader["RoleId"] as int?;
                //obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                obj.Version = reader["Version"] as int?;
                list.Add(obj);
            }
            return list;
         }
        public HW_School_User Mapper(SqlDataReader reader)
         {
            HW_School_User obj=null;
            if (reader.Read())
            {
                obj = new HW_School_User();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.SchoollCode = (string)reader["SchoollCode"];
                obj.RoleId = reader["RoleId"] as int?;
                obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                obj.Version = reader["Version"] as int?;
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_School_User
        /// <summary>
        /// 功能：通过ID得到HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_School_User GetById(string xx_username)
        {            
            string sql = "select * from HW_School_User where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        #endregion
		
		#region##通过主键ID删除HW_School_User
        /// <summary>
        /// 功能：通过主键ID删除HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_School_User where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        public int ResetPassword(SqlTransaction trans, string xx_username, string pass)
        {
            string sql = "update HW_School_User set XX_Pass=@pass where XX_UserName=@xx_username";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pass",pass),
                new SqlParameter("@xx_username",xx_username),
            };
            int result = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, sp);
            return result;
        }
        /// <summary>
        /// 功能：通过主键ID删除HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_School_User where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public int DeleteBySchoolCode(SqlTransaction transaction, string schoolCode)
        {
            int i = 0;
            string sql = "delete from HW_School_User where schoollCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",schoolCode)
            };
            i = SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, sp);
            return i;
        }
		#endregion			
		
        #region##添加HW_School_User
        /// <summary>
        /// 功能：添加HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_School_User hw_school_user)
        {
            int result = 0;
            string sql = "insert into HW_School_User(XX_UserName,SchoollCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version) values(@XX_UserName,@SchoollCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_school_user.XX_UserName),
				new SqlParameter("@SchoollCode",hw_school_user.SchoollCode),
				new SqlParameter("@RoleId",hw_school_user.RoleId),
				new SqlParameter("@XX_Pass",hw_school_user.XX_Pass),
				new SqlParameter("@CNName",hw_school_user.CNName),
				new SqlParameter("@ErrorTimes",hw_school_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_school_user.LastLoginTime),
				new SqlParameter("@Version",hw_school_user.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_School_User hw_school_user)
        {
            int result = 0;
            string sql = "insert into HW_School_User(XX_UserName,SchoollCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version) values(@XX_UserName,@SchoollCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_school_user.XX_UserName),
				new SqlParameter("@SchoollCode",hw_school_user.SchoollCode),
				new SqlParameter("@RoleId",hw_school_user.RoleId),
				new SqlParameter("@XX_Pass",hw_school_user.XX_Pass),
				new SqlParameter("@CNName",hw_school_user.CNName),
				new SqlParameter("@ErrorTimes",hw_school_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_school_user.LastLoginTime),
				new SqlParameter("@Version",hw_school_user.Version)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_School_User集合
        public IList<HW_School_User> GetBySchoolCode(string schoolCode)
        {
            string sql = @"select * from HW_School_User where SchoollCode=@schoolCode order by XX_UserName desc ";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@schoolCode",schoolCode)
            };
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);

            return MapperList(reader);
        }
        public DataTable GetByOrgCode(int pageIndex,int pageSize,out int count, string orgCode)
        {
            string sql = @"select top(@pageSize) * from HW_School_User a inner join (select * from HW_School where OrgCode=@orgCode) b on a.Schoollcode=b.SchoollCode where XX_UserName not in(
                           select top(@pageSize*@pageIndex) XX_UserName from HW_School_User a inner join (select * from HW_School where OrgCode=@orgCode) b on a.Schoollcode=b.SchoollCode order by XX_UserName desc) order by XX_UserName desc  ";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@orgCode",orgCode)
            };
            string sql2 = @"select count(*) from HW_School_User a inner join (select * from HW_School where OrgCode=@orgCode) b on a.Schoollcode=b.SchoollCode ";
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@orgCode",orgCode)
            };
            DataSet ds= SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable GetBySchoolCode(int pageIndex, int pageSize, out int count, string schoolCode)
        {
            string sql = @"select top(@pageSize) * from HW_School_User where schoollcode=@schoolcode XX_UserName not in(
                           select top(@pageSize*@pageIndex) XX_UserName from HW_School_User where schoollcode=@schoolCode order by XX_UserName desc) order by XX_UserName desc  ";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@schoolCode",schoolCode)
            };
            string sql2 = @"select count(*) from HW_School_User where Schoollcode=@schoolCode ";
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@schoolCode",schoolCode)
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public string GetMaxUser(string schoolcode)
        {
            string sql = @"select max(XX_UserName) from HW_XXX_User where roleid=3000 and XX_UserName like @schoolcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@schoolcode",schoolcode+"%")
            };
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql,sp);
            return o.ToString();
        }
		/// <summary>
        /// 功能：得到HW_School_User集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_School_User> GetList()
		{
			string sql = "select * from HW_School_User";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改HW_School_User
        /// <summary>
        /// 功能：修改HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_School_User(HW_School_User hw_school_user)
        {
            int result = 0;
            string sql = "update HW_School_User set XX_UserName = @XX_UserName,SchoollCode = @SchoollCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_school_user.XX_UserName),
				new SqlParameter("@SchoollCode",hw_school_user.SchoollCode),
				new SqlParameter("@RoleId",hw_school_user.RoleId),
				new SqlParameter("@XX_Pass",hw_school_user.XX_Pass),
				new SqlParameter("@CNName",hw_school_user.CNName),
				new SqlParameter("@ErrorTimes",hw_school_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_school_user.LastLoginTime),
				new SqlParameter("@Version",hw_school_user.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_School_User(SqlTransaction transaction,HW_School_User hw_school_user)
        {
            int result = 0;
            string sql = "update HW_School_User set XX_UserName = @XX_UserName,SchoollCode = @SchoollCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_school_user.XX_UserName),
				new SqlParameter("@SchoollCode",hw_school_user.SchoollCode),
				new SqlParameter("@RoleId",hw_school_user.RoleId),
				new SqlParameter("@XX_Pass",hw_school_user.XX_Pass),
				new SqlParameter("@CNName",hw_school_user.CNName),
				new SqlParameter("@ErrorTimes",hw_school_user.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_school_user.LastLoginTime),
				new SqlParameter("@Version",hw_school_user.Version)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_School_User
		/// <summary>
        /// 修改HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_School_User";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_School_User
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(XX_UserName) from HW_School_User";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_School_User
        public HW_School_User GetByUserName(string userName)
        {
            string sql = @"select * from HW_School_User a inner join HW_School b on a.SchoollCode=b.SchoollCode
                           where a.XX_UserName=@userName";
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@userName",userName),
            };
            SqlDataReader reader= SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            HW_School_User schoolUser = Mapper(reader);
            HW_SchoolService schoolService=new HW_SchoolService ();
            HW_School school= schoolService.Mapper(reader);
            schoolUser.hw_School = school;
            //DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            //return ds.Tables[0];
            return schoolUser;
        }
        public DataTable GetSchoolUser(int pageIndex, int pageSize, out int count, string districtCode)
        {
            string sql = @"
                ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP (@pageSize) a.SchoollCode,XX_UserName,a.CNName,b.Name,CNName,OfficePhone,SchoolCharacter,SchoolType FROM dbo.HW_School_User a INNER JOIN dbo.HW_School b ON a.SchoollCode=b.SchoollCode
INNER JOIN m c ON b.DistrictCode=c.DistrictCode where  a.XX_UserName not in (select top
(@pageSize*(@pageIndex)) XX_UserName from HW_School_User z inner join HW_School x on z.SchoollCode=x.SchoollCode INNER JOIN m y ON x.DistrictCode=y.DistrictCode
ORDER BY XX_UserName) ORDER BY a.XX_UserName";
            string sql2 = @" ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT count(XX_UserName) FROM dbo.HW_School_User a INNER JOIN dbo.HW_School b ON a.SchoollCode=b.SchoollCode where b.DistrictCode in(select DistrictCode from m)";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@districtcode",districtCode),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {       
                new SqlParameter("@districtcode",districtCode),
            };
            count = (int)SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
        public DataTable Search(int pageIndex, int pageSize, out int count,string districtCode, string searchString)
        {
            string sql = @"
                ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT TOP (@pageSize) a.SchoollCode,XX_UserName,a.CNName,b.Name,CNName,OfficePhone,SchoolCharacter,SchoolType FROM dbo.HW_School_User a INNER JOIN dbo.HW_School b ON a.SchoollCode=b.SchoollCode
INNER JOIN m c ON b.DistrictCode=c.DistrictCode where (a.CNName LIKE @searchString OR b.Name LIKE @searchString) AND a.XX_UserName not in (select top
(@pageSize*(@pageIndex)) XX_UserName from HW_School_User z inner join HW_School x on z.SchoollCode=x.SchoollCode INNER JOIN m y ON x.DistrictCode=y.DistrictCode
 where z.CNName LIKE @searchString OR x.Name LIKE @searchString ORDER BY XX_UserName) ORDER BY a.XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@districtcode",districtCode),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            string sql2 = @"             ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT count(XX_UserName) FROM dbo.HW_School_User a INNER JOIN dbo.HW_School b ON a.SchoollCode=b.SchoollCode where b.DistrictCode in(select DistrictCode from m)
and (a.CNName LIKE @searchString OR b.Name LIKE @searchString)";
            SqlParameter[] sp2 = new SqlParameter[]
            {       
                new SqlParameter("@districtcode",districtCode),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
            count =(int) SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
        #endregion
                
    }
}
